var gen = require('./util');

var vals = "('{0}', '{1}', '{2}', '{3}',  now())";
var sql = ("INSERT INTO reim_project(comp_id, comp_name, project_name,  admin_staff, create_time) VALUES ");

const xlsx = require('xlsx');
let workbook = xlsx.readFile('../../test/resources/project.xls');
let sheetNames = workbook.SheetNames;
// 获取第一个workSheet
let sheet1 = workbook.Sheets[sheetNames[0]];
let range = xlsx.utils.decode_range(sheet1['!ref']);
//循环获取单元格值
for (let R = range.s.r; R <= range.e.r; ++R) {
    if (R == 0) {
        continue
    }
    let row_value = '';
    for (let C = range.s.c; C <= range.e.c; ++C) {
        let cell_address = {c: C, r: R}; //获取单元格地址
        let cell = xlsx.utils.encode_cell(cell_address); //根据单元格地址获取单元格
        //获取单元格值
        if (sheet1[cell]) {
            // 如果出现乱码可以使用iconv-lite进行转码
            // row_value += iconv.decode(sheet1[cell].v, 'gbk') + ", ";
            row_value += sheet1[cell].v + ", ";
        }
        else {
            row_value += ", ";
        }
    }
    if (row_value.length <= 10) {
        continue
    }
    let col = row_value.split(",")
    let comp = col[0], projectName = col[1]
    let formatData = vals.format(gen.getCompId(comp), comp, projectName, col[2], col[3]);
    sql += formatData + ",\n";
    // console.log(row_value);
}

console.log(sql.substring(0, sql.lastIndexOf(",")));

